﻿using AutoMapper;
using AutoMapper.Execution;
using HongYeSystemWebApi.Templet.Entity.ApiResult;
using HongYeSystemWebApi.Templet.Entity.Entities;
using HongYeSystemWebApi.Templet.Entity.Request;
using HongYeSystemWebApi.Templet.Entity.Request.SysExpenditure;
using HongYeSystemWebApi.Templet.Entity.Request.SysProject;
using HongYeSystemWebApi.Templet.Entity.Response;
using HongYeSystemWebApi.Templet.Entity.Response.Project;
using HongYeSystemWebApi.Templet.Entity.Response.SysCostType;
using HongYeSystemWebApi.Templet.Entity.Response.SysExpenditure;
using HongYeSystemWebApi.Templet.IService;
using MySqlX.XDevAPI.Common;
using OfficeOpenXml;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace HongYeSystemWebApi.Templet.Service
{
    public class ExpenditureService : IExpenditureService
    {
        private readonly SqlSugarClient _db;
        private readonly CommandService _comm;
        private readonly IMapper _mapper;

        /// <summary>
        /// 构造函数依赖注入
        /// </summary>
        /// <param name="db"></param>
        public ExpenditureService(IHelpDB db, IMapper mapper)
        {
            this._db = db.OpenDB();
            _mapper = mapper;
            _comm = new CommandService();
        }
        public async Task DeleteAsync(MultiIdRequest request)
        {
            List<Expenditure> DelData = new List<Expenditure>();

            foreach (var item in request.Ids)
            {
                var project = await _db.Queryable<Expenditure>().FirstAsync(a => a.Id == item);
                project.Delete = true;
                project.OperatDate = DateTime.Now;
                DelData.Add(project);

            }
            await _db.Updateable(DelData).WhereColumns(it => new { it.Id }).ExecuteCommandAsync();
        }
        public async Task InsertAsync(SysExpenditureInsertRequest request)
        {
            if (request.MoneyIn != 0)
            {
                var companyentity = await _db.Queryable<CompanyEntity>().FirstAsync(a => a.Id == request.CompanyId);
                if (!companyentity.CompanyName.Contains("成都鸿业文化"))
                    throw new Exception("现金输入项只能由：成都鸿业文化发展有限公司填写请切换公司后进行的操作 ");
            }
            var data = _mapper.Map<Expenditure>(request);
            data.OperatDate = DateTime.Now;
            await _db.Insertable(data).ExecuteCommandAsync();
        }
        public async Task UpdateAsync(SysExpenditureUpdataRequest request)
        {
            var datafir = await _db.Queryable<Expenditure>().FirstAsync(X => X.Id == request.Id);
            if (datafir == null)
            {
                throw new Exception("未找到修改原始数据");
            }
            var data = _mapper.Map<Expenditure>(request);
            data.OperatDate = DateTime.Now;
            await _db.Updateable(data).WhereColumns(it => new { it.Id }).ExecuteCommandAsync();
        }
        public async Task<PageModel<SysExpenditureDataPageResponse>> GetListAsync(SysExpendituregetRequest request)
        {
            var data = _db.Queryable<Expenditure>().Where(x => !x.Delete);
            if (request.PartyA != 0)
            {
                var parkaId = await _db.Queryable<ProjectEntity>().Where(x => x.PartyA == request.PartyA).Select(x => x.Id).ToListAsync();
                data = data.Where(x => parkaId.Contains(x.ProjectId));
            }
            if (request.ProjectId != 0)
            {
                data = data.Where(x => x.ProjectId == request.ProjectId);
            }
            if (request.CompanyId != 0)
            {
                data = data.Where(x => x.CompanyId == request.CompanyId);
            }
            if (request.StartDate.HasValue)
            {
                data = data.Where(x => x.CreateDate.Date >= request.StartDate.Value.Date);
            }
            if (request.EndDate.HasValue)
            {
                data = data.Where(x => x.CreateDate.Date <= request.EndDate.Value.Date);
            }
            if (request.SupplierId != 0)
            {
                data = data.Where(x => x.SupplierId == request.SupplierId);
            }
            if (request.CostTypeId != 0)
            {
                data = data.Where(x => x.CostTypeId == request.CostTypeId);
            }
            if (!string.IsNullOrWhiteSpace(request.Abstract))
            {
                data = data.Where(x => x.Abstract.Contains(request.Abstract));
            }
         
            RefAsync<int> Total = 0;
            var resultpage = await data.OrderBy(x => x.CreateDate, OrderByType.Desc).ToPageListAsync(request.PageNumber, request.PageSize, Total);
            var result = _mapper.Map<List<SysExpenditureDataPageResponse>>(resultpage);
            var Data = _db.Queryable<Expenditure>().Where(x => !x.Delete).ToList();
            foreach (var item in result)
            {
                var Expenditureinfo = Data.Where(x => x.CreateDate <= item.CreateDate && !x.Delete);
                item.MoneyBalance = Expenditureinfo.Sum(x => x.MoneyIn) - Expenditureinfo.Sum(x => x.MoneyOut);
               // item.MoneyBalance = item.CompanyId == 12 ? Expenditureinfo.Sum(x => x.MoneyIn) - Expenditureinfo.Sum(x => x.MoneyOut) : 0;
               // if ((request.ProjectId != 0) || (request.CompanyId != 0))
               // {
               //     item.MasterBankBalance = 0;
               //     item.MasterBalance = 0;
               // }
               // else
                {
                    item.MasterBankBalance = Math.Round(Expenditureinfo.Sum(x => x.BankIn) - Expenditureinfo.Sum(x => x.BankOut),2);
                    item.MasterBalance = Math.Round(item.MasterBankBalance + item.MoneyBalance,2);
                }
                item.BankBalance = Math.Round(Expenditureinfo.Where(x => x.BankId == item.BankId).Sum(x => x.BankIn) - Expenditureinfo.Where(x => x.BankId == item.BankId).Sum(x => x.BankOut), 2); ;
            }
            return new PageModel<SysExpenditureDataPageResponse>() { Data = result, Total = Total };
        }

        public async Task<ApiResult> ExportAll()
        {
            var request = new SysExpendituregetRequest();
            request.PageSize = int.MaxValue;
            request.PageNumber = 1;
            request.SupplierId = 0;
            request.StartDate = null; request.EndDate = null;
            request.ProjectId = 0; request.CompanyId = 0;
            var result = GetListAsync(request);
            return await ExportCsv(result);
        }

        public async Task<ApiResult> ExportByGetListAsync(SysExpendituregetRequest request)
        {
            var result = GetListAsync(request);
            return await ExportCsv(result);
        }
        public async Task<ApiResult> ExportCsv(Task<PageModel<SysExpenditureDataPageResponse>> result)
        {

            List<List<string>> data = new List<List<string>>();
            var companys = await _db.Queryable<CompanyEntity>().ToListAsync();
            var projects = await _db.Queryable<ProjectEntity>().ToListAsync();
            var suppliers = await _db.Queryable<Supplier>().ToListAsync();
            var costType = await _db.Queryable<CostType>().ToListAsync();
            if (result.Result.Data.Count == 0)
            {
                throw new Exception("当前条件下没有任何数据");
            }
            string currentPath = System.IO.Directory.GetCurrentDirectory();
            var datepath = DateTime.Now.ToString("yyyyMMddHHmmss");
            var fileNamepath = Path.Combine("ExcelFile", $"{datepath}Expenditure.xlsx");
            var filePath = Path.Combine(currentPath, fileNamepath);
            var dirPath = Path.Combine(currentPath, "ExcelFile");

            decimal BankOut = 0;
            decimal MoneyOut = 0;
            decimal BankIn = 0;
            decimal MoneyIn = 0;

            var Title = "日期,公司,项目编号,项目名称,业主单位,经办人/供应,类别,摘要,银行支出（对公）,现金支出,银行收入,现金收入,银行卡余额,现金余额,主银行卡余额,总余额,发票情况,合同".Split(',').ToList();
            data.Add(Title);
            foreach (var item in result.Result.Data)
            {
                var val = new List<string>();
                val.Add(item.CreateDate.ToString("yyyy-MM-dd"));
                val.Add(companys.FindLast(x => x.Id == item.CompanyId)?.CompanyName);
                val.Add(projects.FindLast(x => x.Id == item.ProjectId)?.ProjectNumber);
                val.Add(projects.FindLast(x => x.Id == item.ProjectId)?.ProjectName);
                val.Add(suppliers.FindLast(x => x.Id == projects.FindLast(x => x.Id == item.ProjectId)?.PartyA)?.Name);
                val.Add(suppliers.FindLast(x => x.Id == item.SupplierId)?.Name);
                val.Add(costType.FindLast(x => x.Id == item.CostTypeId)?.CostName);
                val.Add(item.Abstract);
                val.Add(item.BankOut.ToString());
                val.Add(item.MoneyOut.ToString());
                val.Add(item.BankIn.ToString());
                val.Add(item.MoneyIn.ToString());
                val.Add(item.BankBalance.ToString());
                val.Add(item.MoneyBalance.ToString());
                val.Add(item.MasterBankBalance.ToString());
                val.Add(item.MasterBalance.ToString());
                val.Add(item.invoice == 1 ? "有发票" : "无发票");
                val.Add(item.contract == 1 ? "有合同" : "无合同");
                data.Add(val);
                BankOut += item.BankOut;
                MoneyOut += item.MoneyOut;
                BankIn += item.BankIn;
                MoneyIn += item.MoneyIn;
            }
            data.Add(new List<string>() { "合计", "", "", "", "", "", "", "", BankOut.ToString("0.00"), MoneyOut.ToString("0.00"), BankIn.ToString("0.00"), MoneyIn.ToString("0.00") });
            ExcelPackage.LicenseContext = LicenseContext.Commercial;
            // 创建一个Excel文件
            FileInfo existingFile = new FileInfo(filePath);
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

                for (int i = 0; i < data.Count; i++)
                {
                    int index = 0;
                    for (char column = 'A'; column <= Convert.ToChar(_comm.GetColumnLetter(Title.Count)); column++)
                    {
                        if (index < data[i].Count)
                        {
                            var datavalue = data[i][index] == null ? "" : data[i][index];
                            var cells = column.ToString() + (i + 1) + "";
                            worksheet.Cells[cells].Value = datavalue;
                            index++;
                        }
                    }
                }

                var cloums = _comm.GetColumnLetter(Title.Count).ToString() + (data.Count).ToString();
                // 设置边框
                worksheet.Cells[$"A1:{cloums}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells[$"A1:{cloums}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells[$"A1:{cloums}"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells[$"A1:{cloums}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells.AutoFitColumns(); // 对整个工作表自适应
                worksheet.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // 水平居中
                worksheet.Cells.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; // 垂直居中
                // 保存修改后的Excel文件
                package.Save();
                _comm.DeleteFile(dirPath);
            }
            return ApiResult.True(fileNamepath);
        }


    }
}
